package csu.web.mypetstore.persistence.impl;

import csu.web.mypetstore.domain.Order;
import csu.web.mypetstore.persistence.DBUtil;
import csu.web.mypetstore.persistence.OrderDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

public class OrderDaoImpl implements OrderDao {
    private static final String GET_ORDER = "select BILLADDR1, BILLADDR2," +
            " BILLCITY," +
            " BILLCOUNTRY," +
            " BILLSTATE," +
            " BILLTOFIRSTNAME," +
            " BILLTOLASTNAME," +
            " BILLZIP," +
            " CARDTYPE," +
            " CREDITCARD," +
            " EXPRDATE," +
            " ORDERDATE" +
            " FROM ORDERS" +
            " WHERE ORDERID=?";

    private static final String GET_ORDER_LIST = " SELECT" +
            " BILLADDR1," +
            " BILLADDR2," +
            " BILLCITY," +
            " BILLCOUNTRY," +
            " BILLSTATE," +
            " BILLTOFIRSTNAME," +
            " BILLTOLASTNAME," +
            " BILLZIP," +
/*            " SHIPADDR1 AS shipAddress1," +
            " SHIPADDR2 AS shipAddress2," +
            " SHIPCITY," +
            " SHIPCOUNTRY," +
            " SHIPSTATE," +
            " SHIPTOFIRSTNAME," +
            " SHIPTOLASTNAME," +
            " SHIPZIP," +*/
            " CARDTYPE," +
            /*            " COURIER," +*/
            " CREDITCARD," +
            " EXPRDATE AS expiryDate," +
            /*            " LOCALE," +*/
            " ORDERDATE," +
            " ORDERID," +
            " userid" +
            /*            " TOTALPRICE," +*/
            /*            " STATUS" +*/
            " FROM ORDERS ";

    private static final String INSERT_ORDER="INSERT INTO ORDERS ( userid, billaddr1, billaddr2," +
            "billcity,billstate,billzip,billcountry,billtofirstname,billtolastname,creditcard,exprdate,cardtype,orderdate)" +
            " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
    private static final String INSERTORDERSTATUS = " INSERT INTO ORDERSTATUS (ORDERID, LINENUM, TIMESTAMP, STATUS)" +
            " VALUES (#{orderId,jdbcType=NUMERIC}, #{orderId,jdbcType=NUMERIC}, #{orderDate,jdbcType=TIMESTAMP}, #{status,jdbcType=VARCHAR})";


    @Override
    public List<Order> getOrdersByUsername(String username) {
        List<Order> orderList = new ArrayList<Order>();
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(GET_ORDER_LIST);
            ResultSet resultSet = pStatement.executeQuery();
            while(resultSet.next()){
                if (!Objects.equals(resultSet.getString("userid"), username))continue;
                Order order = new Order();
                order.setCardType(resultSet.getString("cardtype"));
                order.setCreditCard(resultSet.getString("creditcard"));
                order.setExpiryDate(resultSet.getString("expirydate"));
                order.setBillToFirstName(resultSet.getString("billtofirstname"));
                order.setBillToLastName(resultSet.getString("billtolastname"));
                order.setBillAddress1(resultSet.getString("billaddr1"));
                order.setBillAddress2(resultSet.getString("billaddr2"));
                order.setBillCity(resultSet.getString("billcity"));
                order.setBillState(resultSet.getString("billstate"));
                order.setBillZip(resultSet.getString("billzip"));
                order.setBillCountry(resultSet.getString("billcountry"));
                order.setOrderDate(resultSet.getString("orderdate"));
                order.setOrderId(resultSet.getInt("orderid"));
                order.setUsername(resultSet.getString("userid"));

                orderList.add(order);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(pStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }

        return orderList;
    }

    @Override
    public Order getOrder(int orderId) {
        Order order = null;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ORDER);
            preparedStatement.setInt(1, orderId);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                order = new Order();
                order.setCardType(resultSet.getString("cardtype"));
                order.setCreditCard(resultSet.getString("creditcard"));
                order.setExpiryDate(resultSet.getString("exprdate"));
                order.setBillToFirstName(resultSet.getString("billtofirstname"));
                order.setBillToLastName(resultSet.getString("billtolastname"));
                order.setBillAddress1(resultSet.getString("billaddr1"));
                order.setBillAddress2(resultSet.getString("billaddr2"));
                order.setBillCity(resultSet.getString("billcity"));
                order.setBillState(resultSet.getString("billstate"));
                order.setBillZip(resultSet.getString("billzip"));
                order.setBillCountry(resultSet.getString("billcountry"));
                order.setOrderId(orderId);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return order;
    }


    @Override
    public void insertOrder(Order order) {
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_ORDER);
            preparedStatement.setString(1, order.getUsername());
            /*            preparedStatement.setDate(2, (Date) order.getOrderDate());*/
            preparedStatement.setString(2,order.getBillAddress1());
            preparedStatement.setString(3,order.getBillAddress2());
            preparedStatement.setString(4,order.getBillCity());
            preparedStatement.setString(5,order.getBillState());
            preparedStatement.setString(6,order.getBillZip());
            preparedStatement.setString(7,order.getBillCountry());
            preparedStatement.setString(8,order.getBillToFirstName());
            preparedStatement.setString(9,order.getBillToLastName());
            preparedStatement.setString(10,order.getCreditCard());
            preparedStatement.setString(11,order.getExpiryDate());
            preparedStatement.setString(12,order.getCardType());
/*            Date date = new Date();
            SimpleDateFormat dateFormat= new SimpleDateFormat("yyyy-MM-dd :hh:mm:ss");*/
            preparedStatement.setString(13,order.getOrderDate());
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void insertOrderStatus(Order order) {

    }
}
